# import xlrd
import pprint
import hashlib
from openpyxl import load_workbook
from graphviz import Digraph


# 生成结点键值
def node_key(ntype, value):
    return  "{}_{}".format(ntype, value)

#生成结点ID
def gen_id(node_key):
    return hashlib.md5(node_key.encode('utf-8')).hexdigest()

#判断结点是否是某个合并结点的成员,并返回那个合并结点
def found_in_comb_nodes(nk, cnds):
    for cnd in cnds:
        if cnd['children'] is not None and nk in cnd['children']:
            return cnd
    return None

# 生成成对的关系
def build_edges(data, comb_nodes):
    edges = list()
    for row in [d for d in data if d['rs'] != '=']:
        l_key = node_key(row['l_ntype'], row['l_value'])
        r_key = node_key(row['r_ntype'], row['r_value'])

        cnd = found_in_comb_nodes(l_key, comb_nodes)
        row['l_id'] = gen_id(l_key) if cnd is None else cnd['id']
        row['l_label'] = gen_html([[row['l_ntype'], row['l_value']],]) if cnd is None else cnd['html'] 

        cnd = found_in_comb_nodes(r_key, comb_nodes)
        row['r_id'] = gen_id(r_key) if cnd is None else cnd['id']
        row['r_label'] = gen_html([[row['r_ntype'], row['r_value']],]) if cnd is None else cnd['html']

        edges.append(row)
    return edges

# 为结点生成显示的html代码
def gen_html(rows):
    html = []
    html.append('<<TABLE BORDER="{}">'.format(1))
    if rows and len(rows) > 0:
        for v in rows:
            html.append('<TR><TD>{}</TD><TD>{}</TD></TR>'.format(v[0], v[1]))
    else:
        html.append('<TR><TD>#NULL</TD><TD>#NULL</TD></TR>')
        
    html.append('</TABLE>>')

    return ''.join(html)

# 合并关系为 = 的左右结点
def build_comb_nodes(data):
    comb_nodes = list()
    for row in data:
        if row['rs'] == '=':
            found_in_prev_comb_node = False
            l_key = node_key(row['l_ntype'], row['l_value'])
            r_key = node_key(row['r_ntype'], row['r_value'])
            for cn in comb_nodes:
                if l_key in cn['children']:
                    cn['children'][r_key] = row['r_ntype'], row['r_value']
                if r_key in cn['children']:
                    cn['children'][l_key] = row['l_ntype'], row['l_value']
                found_in_prev_comb_node = l_key  in cn['children'] or r_key  in cn['children']
            if not found_in_prev_comb_node:
                node = {}
                children = {}
                children[l_key] = [row['l_ntype'], row['l_value']]
                children[r_key] = [row['r_ntype'], row['r_value']]
                node['children'] = children
                node['id'] = gen_id('{}_{}'.format(l_key, r_key))
                node['html'] = gen_html(children.values())
                comb_nodes.append(node)
    return comb_nodes

# 将excel数据读取到结构化的数据列表中
def read_xlsx(xlsx_filename):
    data = list()
    wb = load_workbook(filename=xlsx_filename)
    ws = wb.active
    prev_r_ntype = ''
    prev_r_value = ''
    for r in ws.iter_rows(min_row=2, max_col=6):
        obj = {}
        obj['l_ntype'] = prev_r_ntype if r[0].value is None else r[0].value
        obj['l_value'] =  prev_r_value if r[1].value is None else r[1].value

        obj['rs'] = r[2].value
                
        obj['r_ntype'] = r[3].value
        obj['r_value'] = r[4].value
        prev_r_ntype = r[3].value
        prev_r_value = r[4].value

        # 右结点必须有
        if obj['r_ntype'] is not None and obj['r_value'] is not None:
            data.append(obj)

    return data


#渲染图
def render_graph(edges, render_to):
    dot = Digraph(comment='graph for 129')

    dot.attr('edge', fontname='DengXian')
    dot.attr('node', fontname='DengXian')

    nodes = {}
    for edge in edges:
        l_id = edge['l_id']
        r_id = edge['r_id']
        if l_id not in nodes:
            nodes[l_id] = edge['l_label']
        if r_id not in nodes:
            nodes[r_id] = edge['r_label']
    
    for nid, html in nodes.items():
        dot.node(nid, label=html)
    
    for edge in edges:
        dot.edge(edge['l_id'], edge['r_id'], label=edge['rs'])
    
    dot.render(render_to, format='png')


########### main #############
d = read_xlsx('data.xlsx')
cnds = build_comb_nodes(d)
edges = build_edges(d, cnds)

render_graph(edges, '129mind')

for e in edges:
    pprint.pprint(e)

